package com.ssy.lingxi.dataauth.builder;

import com.ssy.lingxi.dataauth.model.constant.DataAuthType;
import com.ssy.lingxi.dataauth.model.dto.ChannelAuthMemberDto;
import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.operators.conditional.AndExpression;
import net.sf.jsqlparser.expression.operators.conditional.OrExpression;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.Table;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.select.PlainSelect;
import net.sf.jsqlparser.statement.select.Select;
import net.sf.jsqlparser.util.TablesNamesFinder;
import org.springframework.util.CollectionUtils;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.stream.Collectors;

/**
 * PgSql select语句构造器
 * @author 万宁
 * @version 2.0.0
 * @date 2021-04-08
 */
public class SimpleSelectBuilder extends AbstractSqlBuilder<SimpleSelectBuilder> implements ISelectMothod<SimpleSelectBuilder> {

    /**
     * 被拦截的原Sql语句
     */
    private String interceptedSql;

    /**
     * 数据权限拦截类型
     */
    private DataAuthType dataAuthType;

    /**
     * 指定的表名列表
     */
    private List<String> specifyTables;

    /**
     * 查询的表名（会包含join的表名）
     */
    private List<String> tableNames;

    /**
     * 查询的表名，即 tableNames中的第一个元素
     */
    private String tableName;

    /**
     * 表的Schema
     */
    private String schemaName;

    /**
     * 表别名
     */
    private String tableAlias;

    /**
     * 原有的where查询条件
     */
    private String originalWhereClause;

    /**
     * 要添加的用户id的查询条件
     */
    private String plusUserWhereClause;

    /**
     * 要添加的下级渠道会员的查询条件
     */
    private String plusChannelWhereClause;

    /**
     * 构建后的实际执行的Sql语句
     */
    private String execSql;

    /**
     * JSqlParser对象
     */
    private PlainSelect plainSelect;

    /**
     * sql是否被拦截修改
     */
    private boolean hasIntercepted;

    public SimpleSelectBuilder() {
        this.interceptedSql = "";
        this.dataAuthType = DataAuthType.USER;
        this.specifyTables = new ArrayList<>();
        this.tableNames = new ArrayList<>();
        this.tableName = "";
        this.tableAlias = "";
        this.schemaName = "";
        this.plusUserWhereClause = "";
        this.plusChannelWhereClause = "";
        this.execSql = "";
        this.plainSelect = new PlainSelect();
        this.hasIntercepted = false;
    }

    /**
     * 传入被拦截的sql语句
     * @param sql 被拦截的sql语句
     * @return 当前实例
     */
    @Override
    public SimpleSelectBuilder fromSql(String sql) {
        try {
            this.interceptedSql = sql;
            //初始化JSqlParser对象
            Statement stmt = CCJSqlParserUtil.parse(sql);
            Select selectStatement = (Select) stmt;
            this.plainSelect = (PlainSelect) selectStatement.getSelectBody();

            //原有的查询条件
            this.originalWhereClause = this.plainSelect.getWhere() == null ? "" : this.plainSelect.getWhere().toString().toLowerCase();

            //获取查询的表名、Schema
            //使用TablesNamesFinder查询表名，会自动添加schema
            TablesNamesFinder tablesNamesFinder = new TablesNamesFinder();
            this.tableNames = tablesNamesFinder.getTableList(selectStatement);

            Table table = (Table)plainSelect.getFromItem();
            this.tableAlias = table.getAlias() == null ? "" : table.getAlias().getName();
            this.schemaName = table.getSchemaName() == null ? "" : table.getSchemaName();
            //这种方式获取表名，不会带有schema
            this.tableName = table.getName() == null ? "" : ("".equals(this.schemaName) ? table.getName() : this.schemaName.concat(".").concat(table.getName()));

            return this;
        } catch (Exception e) {
            e.printStackTrace();
            return this;
        }
    }

    /**
     * 指定拦截类型
     *
     * @param dataAuthType 拦截类型枚举
     * @return 当前实例
     */
    @Override
    public SimpleSelectBuilder specifyType(DataAuthType dataAuthType) {
        this.dataAuthType = dataAuthType;
        return this;
    }

    /**
     * 指定要进行处理的表名
     * @param dbTableName 要添加数据权限的表名
     * @return 当前实例
     */
    @Override
    public SimpleSelectBuilder specifyTable(String dbTableName) {
        if(dbTableName == null || "".equals(dbTableName)) {
            return this;
        }

        String[] tables = dbTableName.split(",");
        Arrays.stream(tables).forEach(table -> {
            if(!"".equals(this.schemaName) && !table.startsWith(this.schemaName)) {
                this.specifyTables.add(this.schemaName.concat(".").concat(table));
            } else {
                this.specifyTables.add(table);
            }
        });

        return this;
    }

    /**
     * 指定要进行处理的Entity类
     * @param entityClass Jpa Entity的Class
     * @return 当前实例
     */
    @Override
    public SimpleSelectBuilder specifyTable(Class<?> entityClass) {
        if(entityClass != null) {
            javax.persistence.Table annotation = entityClass.getAnnotation(javax.persistence.Table.class);
            if (annotation != null) {
                String schema = "".equals(annotation.schema()) ? "" : annotation.schema().concat(".");
                String tableName = annotation.name();
                this.specifyTables.add(schema.concat(tableName));
            }
        }

        return this;
    }

    /**
     * 添加where语句的查询条件
     *
     * @param memberIdColName 会员Id字段名称
     * @param roleIdColName   角色Id字段名称
     * @param userIdColName   用户Id字段名称
     * @param userIds         用户Id列表
     * @param members         会员自身及下属渠道会员Id、角色Id列表
     * @return 要添加的where查询条件
     */
    @Override
    public SimpleSelectBuilder addWhere(String memberIdColName, String roleIdColName, String userIdColName, List<Long> userIds, List<ChannelAuthMemberDto> members) {
        String tableAliasPrefix = this.tableAlias != null && !"".equals(this.tableAlias.trim()) ? this.tableAlias.concat(".") : "";
        if(!CollectionUtils.isEmpty(userIds)) {
            if(userIds.size() == 1) {
                this.plusUserWhereClause = tableAliasPrefix.concat(userIdColName).concat(" = ").concat(String.valueOf(userIds.get(0)));
            } else {
                this.plusUserWhereClause = tableAliasPrefix.concat(userIdColName).concat(" in ").concat("(".concat(userIds.stream().map(String::valueOf).collect(Collectors.joining(","))).concat(")"));
            }
        }

        if(!CollectionUtils.isEmpty(members)) {
            this.plusChannelWhereClause = "(".concat(tableAliasPrefix.concat(memberIdColName)).concat(",").concat(tableAliasPrefix.concat(roleIdColName)).concat(") in (").concat(members.stream().map(model -> "(" + model.getMemberId() + "," + model.getRoleId() + ")").collect(Collectors.joining(","))).concat(")");
        }

        return this;
    }

    /**
     * 触发sql语句的构建（拼接）
     * @return 当前实例
     */
    @Override
    public SimpleSelectBuilder buildSql() {
        //如果有指定的表，则只对指定表进行处理
        if(this.specifyTables.size() > 0 && !this.specifyTables.contains(this.tableName)) {
            this.execSql = this.interceptedSql;
            return this;
        }

        //根据拦截类型构建拦截后的sql语句
        try {
            switch (this.dataAuthType) {
                case USER:
                    buildUserAuthSql();
                    break;
                case CHANNEL:
                    buildChannelAuthSql();
                    break;
                default:
                    buildAuthSql();
                    break;
            }
        } catch (Exception e) {
            e.printStackTrace();
        }

        return this;
    }

    /**
     * 当拦截类型是用户权限时，构建sql
     */
    private void buildUserAuthSql() throws JSQLParserException {
        //如果User条件不为空
        if(!"".equals(this.plusUserWhereClause)) {
            if(!"".equals(this.originalWhereClause)) {
                if (this.originalWhereClause.contains(" or ")) {
                    Expression andExp = CCJSqlParserUtil.parseCondExpression("(".concat(this.originalWhereClause).concat(") and ").concat(this.plusUserWhereClause));
                    this.plainSelect.setWhere(andExp);
                } else {
                    this.plainSelect.setWhere(new AndExpression(this.plainSelect.getWhere(), CCJSqlParserUtil.parseCondExpression(this.plusUserWhereClause)));
                }
            } else {
                this.plainSelect.setWhere(CCJSqlParserUtil.parseCondExpression(this.plusUserWhereClause));
            }

            this.hasIntercepted = true;
        }

        this.execSql = plainSelect.toString().toLowerCase();
    }

    /**
     * 当拦截类型是渠道权限时，构建sql
     */
    private void buildChannelAuthSql() throws JSQLParserException {
        if(!"".equals(this.plusChannelWhereClause)) {
            if(!"".equals(this.originalWhereClause)) {
                if (this.originalWhereClause.contains(" or ")) {
                    Expression andExp = CCJSqlParserUtil.parseCondExpression("(".concat(this.originalWhereClause).concat(") and ").concat(this.plusChannelWhereClause));
                    this.plainSelect.setWhere(andExp);
                } else {
                    this.plainSelect.setWhere(new AndExpression(this.plainSelect.getWhere(), CCJSqlParserUtil.parseCondExpression(this.plusChannelWhereClause)));
                }
            } else {
                this.plainSelect.setWhere(CCJSqlParserUtil.parseCondExpression(this.plusChannelWhereClause));
            }

            this.hasIntercepted = true;
        }

        this.execSql = plainSelect.toString().toLowerCase();
    }

    /**
     * 当拦截类型是用户+渠道权限时，构建sql
     */
    private void buildAuthSql() throws JSQLParserException {
        //如果User条件不为空
        if(!"".equals(this.plusUserWhereClause)) {
            //如果下级会员条件不为空
            if(!"".equals(this.plusChannelWhereClause)) {
                //如果原查询条件不为空
                if(!"".equals(this.originalWhereClause)) {
                    //如果原查询条件中包含了逻辑 or，则将原查询条件用小括号括起来
                    if(this.originalWhereClause.contains(" or ")) {
                        Expression andExp = CCJSqlParserUtil.parseCondExpression( "(".concat(this.originalWhereClause).concat(") and ").concat(this.plusUserWhereClause));
                        this.plainSelect.setWhere(new OrExpression(andExp, CCJSqlParserUtil.parseCondExpression(this.plusChannelWhereClause)));
                    } else {
                        this.plainSelect.setWhere(new AndExpression(this.plainSelect.getWhere(), CCJSqlParserUtil.parseCondExpression(this.plusUserWhereClause)));
                        this.plainSelect.setWhere(new OrExpression(this.plainSelect.getWhere(), CCJSqlParserUtil.parseCondExpression(this.plusChannelWhereClause)));
                    }
                } else {
                    this.plainSelect.setWhere(CCJSqlParserUtil.parseCondExpression(this.plusUserWhereClause));
                    this.plainSelect.setWhere(new OrExpression(this.plainSelect.getWhere(), CCJSqlParserUtil.parseCondExpression(this.plusChannelWhereClause)));
                }
            } else {
                if(!"".equals(this.originalWhereClause)) {
                    if (this.originalWhereClause.contains(" or ")) {
                        Expression andExp = CCJSqlParserUtil.parseCondExpression("(".concat(this.originalWhereClause).concat(") and ").concat(this.plusUserWhereClause));
                        this.plainSelect.setWhere(andExp);
                    } else {
                        this.plainSelect.setWhere(new AndExpression(this.plainSelect.getWhere(), CCJSqlParserUtil.parseCondExpression(this.plusUserWhereClause)));
                    }
                } else {
                    this.plainSelect.setWhere(CCJSqlParserUtil.parseCondExpression(this.plusUserWhereClause));
                }
            }

            this.hasIntercepted = true;
        } else {
            if(!"".equals(this.plusChannelWhereClause)) {
                if(!"".equals(this.originalWhereClause)) {
                    if (this.originalWhereClause.contains(" or ")) {
                        Expression andExp = CCJSqlParserUtil.parseCondExpression("(".concat(this.originalWhereClause).concat(") and ").concat(this.plusChannelWhereClause));
                        this.plainSelect.setWhere(andExp);
                    } else {
                        this.plainSelect.setWhere(new AndExpression(this.plainSelect.getWhere(), CCJSqlParserUtil.parseCondExpression(this.plusChannelWhereClause)));
                    }
                } else {
                    this.plainSelect.setWhere(CCJSqlParserUtil.parseCondExpression(this.plusChannelWhereClause));
                }

                this.hasIntercepted = true;
            }
        }

        this.execSql = plainSelect.toString().toLowerCase();
    }

    /**
     * Debug输出构建后的sql语句
     *
     * @return 当前实例
     */
    @Override
    public SimpleSelectBuilder showExecSql() {
        if(this.hasIntercepted) {
            System.out.println("数据拦截 => " + this.execSql);
        }

        return this;
    }

    @Override
    public String toSql() {
        return this.execSql;
    }
}
